# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
# 文件：pro_msydloan_loan
获取中移动项目的放款统计数据：
1.接口未接收但文件已推送放款；2.接口接收到但文件未推送放款; 3.当天办理业务当天退款；4.某段时间的退款；5.文件推送的所有放款
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
import time
import os
from pycacho.cachobase.deal_excel import copy_new_sheet, common_write
from pycacho.cachobase.deal_time import minus_date
from pycacho.cachobase.deal_email import send_email
from msydwork.prod.pro_config import cursor,db

# 套餐办理成功接口数据,昨天日期
def get_blockInfo(count,td_date):
    sql = 'select count(*),sum(loan_amt) from pkg_notice b,pro_main c where b.process_no=c.process_no and substr(acp_tm,1,10)%s"%s";' %(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        info.append('接口收到办理成功订单')
        info.append(result[0])
        info.append(result[1])
        info.append('借据已生成，hb_loanfile_detail显示放款不成功')
    #print(info)
    return info


# 分省业务办理确认文件数据,昨天日期
def get_loanFile(count,td_date):
    sql = 'select count(*),sum(amt) from hb_loanfile_detail where substr(acp_dt,1,10)%s"%s";' %(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        info.append('文件收到办理成功订单')
        info.append(result[0])
        info.append(result[1])
    #print(info)
    return info


# 分省文件有但未收到办理成功通知的订单统计,昨天日期
def get_notBlock(count,td_date):
    sql = 'select count(*),sum(amt) from hb_loanfile_detail where substr(acp_dt,1,10)%s"%s" and mpl_ord_no not in (select brw_ord_no from pkg_notice);' %(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        info.append('文件-接口订单')
        info.append(result[0])
        info.append(result[1])
        info.append('借据未生成')
    #print(info)
    return info


# 收到办理成功通知的订单,但分省文件未收到订单统计,昨天日期
def get_notFile(count,td_date):
    sql = 'select count(*),sum(loan_amt) from pkg_notice b,pro_main c where b.process_no=c.process_no and substr(acp_tm,1,10)%s"%s" and brw_ord_no not in (select mpl_ord_no from hb_loanfile_detail);'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        info.append('接口-文件')
        info.append(result[0])
        info.append(result[1])
    #print(info)
    return info


# 分省文件有的所有订单详情,昨天日期
def get_FileInfo(count,td_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.mpl_ord_no,b.org_ord_no,b.amt,c.pkg_amt,b.dep_id,b.dep_nm,substr(acp_dt,1,10) as 办理成功日期,b.file_prov_no,b.opr_id,b.opr_mbl_no,c.dep_mng_mbl_no from hb_usr a,hb_loanfile_detail b,pro_hb_loan c where substr(acp_dt,1,10)%s"%s" and a.hb_usr_no=b.hb_usr_no and b.mpl_ord_no=c.brw_ord_no order by substr(acp_dt,1,10),b.mpl_ord_no;'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(len(info),info)
    return info


# T日某段时间的退款
def get_yesterday_refund(count,to_day):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.brw_ord_no,b.process_no,c.amt,c.dep_id,c.dep_nm,substr(acp_dt,1,10),c.file_prov_no,b.repay_amt,substr(start_time,1,10) as 退货日期 from hb_usr a,ms_repay_order b, hb_loanfile_detail c where a.hb_usr_no=b.hb_usr_no and b.brw_ord_no=c.mpl_ord_no and substr(start_time,1,10)%s"%s" and rpy_mod=3;'%(count,to_day)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(len(info),info)
    return info


# 分省文件有但未收到办理成功通知的订单详情,昨天日期
def get_notBlockInfo(count,td_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.mpl_ord_no,b.org_ord_no,b.amt,b.dep_id,b.dep_nm,substr(acp_dt,1,10) as 办理成功日期,b.file_prov_no from hb_usr a,hb_loanfile_detail b where substr(acp_dt,1,10)%s"%s" and a.hb_usr_no=b.hb_usr_no and b.mpl_ord_no not in (select brw_ord_no from pkg_notice) order by b.mpl_ord_no;'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(info)
    return info


# 收到办理成功通知的订单,但分省文件未收到订单详情,昨天日期
def get_notFileInfo(count,td_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.brw_ord_no,b.process_no,a.loan_amt,b.dep_id,b.dep_nm,substr(b.acp_tm,1,10),a.usr_prov_no,c.status from hb_usr a,pkg_notice b,ms_loan c where a.hb_usr_no=c.hb_user_no and b.process_no=c.process_no and substr(b.acp_tm,1,10)%s"%s" and c.outside_process_no not in (select mpl_ord_no from hb_loanfile_detail);'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(len(info),info)
    return info


# 当天办理当天退货详情（一定是业务办理成功接口通知过，否则无法进行退货）,昨天日期
def get_refundInfo(count,td_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.brw_ord_no,b.process_no,a.loan_amt,b.dep_id,b.dep_nm,substr(b.acp_tm,1,10),a.usr_prov_no from hb_usr a,pkg_notice b,ms_loan c where a.hb_usr_no=c.hb_user_no and b.process_no=c.process_no and c.paid_out_date=substr(c.borrow_time,1,10) and substr(c.borrow_time,1,10)%s"%s";'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(len(info),info)
    return info


# 文件已推送但借据未生成详情,昨天日期
def get_notLoan(count,td_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.mpl_ord_no,b.org_ord_no,b.amt,b.dep_id,b.dep_nm,substr(acp_dt,1,10)as 办理成功日期,b.file_prov_no from hb_usr a,hb_loanfile_detail b where substr(acp_dt,1,10)%s"%s" and a.hb_usr_no=b.hb_usr_no and b.mpl_ord_no not in (select outside_process_no from ms_loan) order by b.mpl_ord_no;'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(len(info),info)
    return info


# T-2日办理，T-1运营在web端已生成并且导入但hb_loanfile_detail未放款成功（非2-放款成功）,T日统计
def get_loanType(count,yd_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.mpl_ord_no,b.org_ord_no,b.amt,b.dep_id,b.dep_nm,substr(acp_dt,1,10),b.file_prov_no,b.loan_type,c.status from hb_usr a,hb_loanfile_detail b,ms_loan c where substr(acp_dt,1,10)%s"%s" and a.hb_usr_no=b.hb_usr_no and b.mpl_ord_no=c.outside_process_no and b.loan_type<>2 order by loan_type,b.mpl_ord_no;'%(count,yd_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(len(info),info)
    return info


def get_files(count,file_path,sheet_name,old_name,to_day,td_date,yd_date):
    two_list =[]
    two_list.append([td_date+'业务办理成功信息汇总','文件日期' + sheet_name ])
    two_list.append(['\n'])
    # 1条数据
    two_list.append(['备注','数量','金额'])
    block = get_blockInfo(count,td_date)
    loanFile = get_loanFile(count,td_date)
    notBlock = get_notBlock(count,td_date)
    notFile = get_notFile(count,td_date)

    two_list.append(block)
    two_list.append(loanFile)
    two_list.append(notBlock)
    two_list.append(notFile)
    two_list.append(['对账','',''])

    # 多条数据
    if count == '=':
        two_list.append(['\n'])
        fileInfo = get_FileInfo(count, td_date)
        two_list.append(['BOSS文件推送的办理成功所有订单',''])
        two_list.append(['序号','和包号','注册手机号','身份证号','姓名','和包借款订单','小贷借款订单','借款金额','套餐金额','营业厅编号','营业厅名称','办理业务成功日期','业务办理省份','营业员编号','营业员手机号','业务办理营业厅负责人手机号'])
        for info in fileInfo:
            two_list.append(info)

    two_list.append(['\n'])
    yesterday_refund = get_yesterday_refund(count, to_day)
    two_list.append([to_day +'退货的所有订单', ''])
    two_list.append(['序号', '和包号', '注册手机号', '身份证号', '姓名', '和包借款订单', '小贷借款订单','借款金额','营业厅编号','营业厅名称','办理业务成功日期','业务办理省份', '还款金额', '退货日期'])
    for info in yesterday_refund:
        two_list.append(info)

    two_list.append(['\n'])
    refundInfo = get_refundInfo(count, td_date)
    two_list.append(['当天办理成功，当天退货订单', ''])
    two_list.append(['序号', '和包号', '注册手机号', '身份证号', '姓名', '和包借款订单', '小贷借款订单', '还款金额', '营业厅编号', '营业厅名称', '办理业务成功日期', '业务办理省份'])
    for info in refundInfo:
        two_list.append(info)

    if count == '=':
        two_list.append(['\n'])
        notBlockInfo = get_notBlockInfo(count,td_date)
        two_list.append(['BOSS文件推送的办理成功订单，但实时接口未收到办理成功通知',''])
        two_list.append(['序号','和包号','注册手机号','身份证号','姓名','和包借款订单','小贷借款订单','借款金额','营业厅编号','营业厅名称','办理业务成功日期','业务办理省份'])
        for info in notBlockInfo:
            two_list.append(info)

    two_list.append(['\n'])
    notFileInfo = get_notFileInfo(count, td_date)
    two_list.append(['实时接口收到办理成功通知，但BOSS文件未推送', ''])
    two_list.append(['序号', '和包号', '注册手机号', '身份证号', '姓名', '和包借款订单', '小贷借款订单', '借款金额', '营业厅编号', '营业厅名称', '办理业务成功日期', '业务办理省份','借据状态'])
    for info in notFileInfo:
        two_list.append(info)

    two_list.append(['\n'])
    notLoan = get_notLoan(count, td_date)
    two_list.append([td_date + '办理成功订单、接口未通知借据未生成、无法进行退货',''])
    two_list.append(['序号','和包号','注册手机号','身份证号','姓名','和包借款订单','小贷借款订单','借款金额','营业厅编号','营业厅名称','办理业务成功日期','业务办理省份'])
    for info in notLoan:
        two_list.append(info)

    two_list.append(['\n'])
    loanType = get_loanType(count, yd_date)
    two_list.append([yd_date + '办理成功订单、接口已通知借据已生成，hb_loanfile_detail显示放款不成功', ''])
    two_list.append(['序号', '和包号', '注册手机号', '身份证号', '姓名', '和包借款订单', '小贷借款订单', '还款金额', '营业厅编号', '营业厅名称', '办理业务成功日期', '业务办理省份','文件放款状态','借据放款状态'])
    for info in loanType:
        two_list.append(info)

    print(two_list)

    old_file = '中移动对账' + old_name + '.xls'
    if count == '<=':          # 所有数据汇总
        file = '中移动对账汇总' + sheet_name + '.xls'
        common_write(two_list,file_path,file,sheet_name,0,0)
    else:
        file = '中移动对账' + sheet_name + '.xls'      # 某一天
        try:
            copy_new_sheet(two_list,file_path,old_file,file,sheet_name,0,0)
        except:
            common_write(two_list,file_path, file, sheet_name,0,0)

def do():
    sql = 'select count(*) from hb_loanfile_detail where file_date="%s" and loan_type=0;' %(minus_date(0, 't'))
    cursor.execute(sql)
    results = cursor.fetchall()
    num = results[0][0]
    if (os.path.exists(xls_file)):
        print('已生成excel文件，发送过邮件！')
        time.sleep(500000)
    elif num > 0 and not(os.path.exists(xls_file)):
        print('已创建放款文件，待生成excel文件.....')
        main()
    else:
        print('运营暂时还未创建放款文件！')
        time.sleep(500000)
    # 发送邮件
    cursor.close()
    db.close()
    subject = '中移动放款数据统计' + minus_date(0, 'qt')
    content = '中移动放款数据统计' + minus_date(0, 'qt') + '，总共' + str(n) + '天的数据'
    receivers = ["zenglw@rendongholdings.com", "zhuxl@rendongholdings.com"]
    send_email(subject, content, xls_file, receivers,[])


def main():
    for i in range(n):
        t = n - i - 1
        to_day = minus_date(t, 't')  # 文件日期t %Y-%m-%d
        sheet_name = minus_date(t, 's')  # 文件日期s %Y%m%d
        old_name = minus_date(t + 1, 's')

        td_date = minus_date(t + 1, 't')  # 昨天套餐办理成功日期
        yd_date = minus_date(t + 1, 't')  # +2 为前天办理成功、+1为昨天办理成功

        get_files('=',file_path,sheet_name,old_name,to_day,td_date,yd_date)  # = ,<=
        print("文件生成的目录为：" + file_path)
        time.sleep(5)


if __name__ == '__main__':
    file_path = 'E:\zenglingwei\statistics\\'
    xls_file = file_path + '中移动对账' + minus_date(0, 's') + '.xls'
    n = 1
    # n = int(input("请输入数字：截至到今天需要生成文件的天数，比如昨天到今天，输入2，以此类推>："))
    do()

